SQL*Plus 执行脚本时传递参数(@script

2024-06-18 04:26| 来源: 网络整理| 查看: 265

在使用sqlplus执行sql脚本时，经常碰到向脚本传递参数的情形。类似于shell脚本的参数传递，我们同样可以向sql脚本传递参数，其方法是脚本后面直接跟多个连续的参数并以空格分开。本文描述该内容并给出示例。

1、SQLPlus 的帮助信息

[sql] view plain copy print ? 下面的帮助信息是关于sqlplus调用带参脚本的说明 sqlplus -H is: @|[.] [ ...] Runs the specified SQL*Plus script from a web server (URL) or the local file system (filename.ext) with specified parameters that will be assigned to substitution variables in the script.

2、shell 提示符下sqlplus调用带参脚本

[sql] view plain copy print ? SQL> select * from v$version where rownum insert into emp(empno,ename,job) select 8888,'Bob Cheng','DBA' from dual; SQL> commit; [oracle@linux1 ~]$ more test.sql set linesize 160 select empno,ename,job from &1 where upper(ename)=upper('&2'); exit; [oracle@linux1 ~]$ sqlplus scott/tiger@rac11g @test.sql emp scott old 1: select empno,ename,job from &1 where upper(ename)=upper('&2') new 1: select empno,ename,job from emp where upper(ename)=upper('scott') EMPNO ENAME JOB ---------- ---------- --------- 7788 SCOTT ANALYST --注意，对于含有空格的调用需要使用单引号或双引号，如下示例 [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp 'bob cheng' old 1: select empno,ename,job from &1 where upper(ename)=upper('&2') new 1: select empno,ename,job from emp where upper(ename)=upper('bob cheng') EMPNO ENAME JOB ---------- ---------- --------- 8888 Bob Cheng DBA [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp "bob cheng" [sql] view plain copy print ? SQL> select * from v$version where rownum insert into emp(empno,ename,job) select 8888,'Bob Cheng','DBA' from dual; SQL> commit; [oracle@linux1 ~]$ more test.sql set linesize 160 select empno,ename,job from &1 where upper(ename)=upper('&2'); exit; [oracle@linux1 ~]$ sqlplus scott/tiger@rac11g @test.sql emp scott old 1: select empno,ename,job from &1 where upper(ename)=upper('&2') new 1: select empno,ename,job from emp where upper(ename)=upper('scott') EMPNO ENAME JOB ---------- ---------- --------- 7788 SCOTT ANALYST --注意，对于含有空格的调用需要使用单引号或双引号，如下示例 [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp 'bob cheng' old 1: select empno,ename,job from &1 where upper(ename)=upper('&2') new 1: select empno,ename,job from emp where upper(ename)=upper('bob cheng') EMPNO ENAME JOB ---------- ---------- --------- 8888 Bob Cheng DBA [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp "bob cheng" SQL> select * from v$version where rownum insert into emp(empno,ename,job) select 8888,'Bob Cheng','DBA' from dual; SQL> commit; [oracle@linux1 ~]$ more test.sql set linesize 160 select empno,ename,job from &1 where upper(ename)=upper('&2'); exit; [oracle@linux1 ~]$ sqlplus scott/tiger@rac11g @test.sql emp scott old 1: select empno,ename,job from &1 where upper(ename)=upper('&2') new 1: select empno,ename,job from emp where upper(ename)=upper('scott') EMPNO ENAME JOB ---------- ---------- --------- 7788 SCOTT ANALYST --注意，对于含有空格的调用需要使用单引号或双引号，如下示例 [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp 'bob cheng' old 1: select empno,ename,job from &1 where upper(ename)=upper('&2') new 1: select empno,ename,job from emp where upper(ename)=upper('bob cheng') EMPNO ENAME JOB ---------- ---------- --------- 8888 Bob Cheng DBA [oracle@linux1 ~]$ sqlplus scott/tiger @test.sql emp "bob cheng"

3、SQL提示符下调用带参脚本

[sql] view plain copy print ? SQL> @test.sql emp scott SQL> set linesize 160 SQL> select empno,ename,job from &1 where upper(ename)=upper('&2'); EMPNO ENAME JOB ---------- ---------- --------- 7788 SCOTT ANALYST SQL> exit; -->由于脚本中有exit，因此脚本执行后会退出，如果是在SQL提示符下而不需要退出应注释该行 [sql] view plain copy print ? SQL> @test.sql emp scott SQL> set linesize 160 SQL> select empno,ename,job from &1 where upper(ename)=upper('&2'); EMPNO ENAME JOB ---------- ---------- --------- 7788 SCOTT ANALYST SQL> exit; -->由于脚本中有exit，因此脚本执行后会退出，如果是在SQL提示符下而不需要退出应注释该行 SQL> @test.sql emp scott SQL> set linesize 160 SQL> select empno,ename,job from &1 where upper(ename)=upper('&2'); EMPNO ENAME JOB ---------- ---------- --------- 7788 SCOTT ANALYST SQL> exit; -->由于脚本中有exit，因此脚本执行后会退出，如果是在SQL提示符下而不需要退出应注释该行

4、调用plsql时的带参脚本

[sql] view plain copy print ? [oracle@linux1 ~]$ more test2.sql set serveroutput on; declare v_sal number; v_emp number:=&1; begin select sal into v_sal from emp where empno=&1; dbms_output.put_line('The sal is :'||to_char(v_sal)); end; / exit; [oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788 old 3: v_emp number:=&1; new 3: v_emp number:=7788; old 5: select sal into v_sal from emp where empno=&1; new 5: select sal into v_sal from emp where empno=7788; The sal is :3000 PL/SQL procedure successfully completed. [sql] view plain copy print ? [oracle@linux1 ~]$ more test2.sql set serveroutput on; declare v_sal number; v_emp number:=&1; begin select sal into v_sal from emp where empno=&1; dbms_output.put_line('The sal is :'||to_char(v_sal)); end; / exit; [oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788 old 3: v_emp number:=&1; new 3: v_emp number:=7788; old 5: select sal into v_sal from emp where empno=&1; new 5: select sal into v_sal from emp where empno=7788; The sal is :3000 PL/SQL procedure successfully completed. [oracle@linux1 ~]$ more test2.sql set serveroutput on; declare v_sal number; v_emp number:=&1; begin select sal into v_sal from emp where empno=&1; dbms_output.put_line('The sal is :'||to_char(v_sal)); end; / exit; [oracle@linux1 ~]$ sqlplus scott/tiger @test2.sql 7788 old 3: v_emp number:=&1; new 3: v_emp number:=7788; old 5: select sal into v_sal from emp where empno=&1; new 5: select sal into v_sal from emp where empno=7788; The sal is :3000 PL/SQL procedure successfully completed.

5、使用new_value子句

[sql] view plain copy print ? SQL> col ename new_value v_name -->new_value的实质是将得到的结果赋值给v_name以便后续调用 SQL> select ename from emp where empno=7788; ENAME ---------- SCOTT SQL> select ename,job,sal from emp where ename='&v_name'; old 1: select ename,job,sal from emp where ename='&v_name' new 1: select ename,job,sal from emp where ename='SCOTT' ENAME JOB SAL ---------- --------- ---------- SCOTT ANALYST 3000 --交互式方式使用new_value --Author : Robinson Cheng --下面的脚本是从awrrpti.sql摘取部分片段来展示 --Blog : http://blog.csdn.net/robinson_0612 [oracle@linux1 ~]$ more temp.sql prompt prompt Specify the Report Type prompt ~~~~~~~~~~~~~~~~~~~~~~~ prompt Would you like an HTML report, or a plain text report? prompt Enter 'html' for an HTML report, or 'text' for plain text prompt Defaults to 'html' column report_type new_value report_type; -->这个地方定义的new_value是关键 set heading off; select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual; set heading on; prompt "Variable report_type value is :&report_type" SQL> @temp.sql -->执行时出现交互提示 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual new 1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual Type Specified: html "Variable report_type value is :html" -->变量的值传递给了report_type [sql] view plain copy print ? SQL> col ename new_value v_name -->new_value的实质是将得到的结果赋值给v_name以便后续调用 SQL> select ename from emp where empno=7788; ENAME ---------- SCOTT SQL> select ename,job,sal from emp where ename='&v_name'; old 1: select ename,job,sal from emp where ename='&v_name' new 1: select ename,job,sal from emp where ename='SCOTT' ENAME JOB SAL ---------- --------- ---------- SCOTT ANALYST 3000 --交互式方式使用new_value --Author : Robinson Cheng --下面的脚本是从awrrpti.sql摘取部分片段来展示 --Blog : http://blog.csdn.net/robinson_0612 [oracle@linux1 ~]$ more temp.sql prompt prompt Specify the Report Type prompt ~~~~~~~~~~~~~~~~~~~~~~~ prompt Would you like an HTML report, or a plain text report? prompt Enter 'html' for an HTML report, or 'text' for plain text prompt Defaults to 'html' column report_type new_value report_type; -->这个地方定义的new_value是关键 set heading off; select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual; set heading on; prompt "Variable report_type value is :&report_type" SQL> @temp.sql -->执行时出现交互提示 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual new 1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual Type Specified: html "Variable report_type value is :html" -->变量的值传递给了report_type SQL> col ename new_value v_name -->new_value的实质是将得到的结果赋值给v_name以便后续调用 SQL> select ename from emp where empno=7788; ENAME ---------- SCOTT SQL> select ename,job,sal from emp where ename='&v_name'; old 1: select ename,job,sal from emp where ename='&v_name' new 1: select ename,job,sal from emp where ename='SCOTT' ENAME JOB SAL ---------- --------- ---------- SCOTT ANALYST 3000 --交互式方式使用new_value --Author : Robinson Cheng --下面的脚本是从awrrpti.sql摘取部分片段来展示 --Blog : http://blog.csdn.net/robinson_0612 [oracle@linux1 ~]$ more temp.sql prompt prompt Specify the Report Type prompt ~~~~~~~~~~~~~~~~~~~~~~~ prompt Would you like an HTML report, or a plain text report? prompt Enter 'html' for an HTML report, or 'text' for plain text prompt Defaults to 'html' column report_type new_value report_type; -->这个地方定义的new_value是关键 set heading off; select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual; set heading on; prompt "Variable report_type value is :&report_type" SQL> @temp.sql -->执行时出现交互提示 Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html old 1: select 'Type Specified: ',lower(nvl('&&report_type','html')) report_type from dual new 1: select 'Type Specified: ',lower(nvl('html','html')) report_type from dual Type Specified: html "Variable report_type value is :html" -->变量的值传递给了report_type

6、new_value与&符号的综合运用

[sql] view plain copy print ? --下面的这个脚本来自Tom大师，其中包含了new_value以及替代变量的使用 --通过该例子来体会这两种方式 [oracle@linux1 ~]$ more mystat.sql set echo off set verify off column value new_val V -->此处使用了new_value方式来传递变量 define S="&1" -->此处定义了替代变量来接受参数 set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on --下面是第二个脚本 --在第二个脚本中引用到了前一个脚本定义的变量，如&S，&V。实际上&S是一个全局变量 [oracle@linux1 ~]$ more mystat2.sql set echo off set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on SQL> @mystat SQL> set echo off Enter value for 1: parse time elapsed NAME VALUE ------------------------------------------------------ ---------- parse time elapsed 83 SQL> update emp set sal=sal+200; 15 rows updated. SQL> @mystat2 SQL> set echo off linesize 160 NAME V DIFF ------------------------------------------------------ ---------- ---------------- parse time elapsed 117 34 [sql] view plain copy print ? --下面的这个脚本来自Tom大师，其中包含了new_value以及替代变量的使用 --通过该例子来体会这两种方式 [oracle@linux1 ~]$ more mystat.sql set echo off set verify off column value new_val V -->此处使用了new_value方式来传递变量 define S="&1" -->此处定义了替代变量来接受参数 set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on --下面是第二个脚本 --在第二个脚本中引用到了前一个脚本定义的变量，如&S，&V。实际上&S是一个全局变量 [oracle@linux1 ~]$ more mystat2.sql set echo off set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on SQL> @mystat SQL> set echo off Enter value for 1: parse time elapsed NAME VALUE ------------------------------------------------------ ---------- parse time elapsed 83 SQL> update emp set sal=sal+200; 15 rows updated. SQL> @mystat2 SQL> set echo off linesize 160 NAME V DIFF ------------------------------------------------------ ---------- ---------------- parse time elapsed 117 34 --下面的这个脚本来自Tom大师，其中包含了new_value以及替代变量的使用 --通过该例子来体会这两种方式 [oracle@linux1 ~]$ more mystat.sql set echo off set verify off column value new_val V -->此处使用了new_value方式来传递变量 define S="&1" -->此处定义了替代变量来接受参数 set autotrace off select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on --下面是第二个脚本 --在第二个脚本中引用到了前一个脚本定义的变量，如&S，&V。实际上&S是一个全局变量 [oracle@linux1 ~]$ more mystat2.sql set echo off set verify off select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff from v$statname a, v$mystat b where a.statistic# = b.statistic# and lower(a.name) like '%' || lower('&S')||'%' / set echo on SQL> @mystat SQL> set echo off Enter value for 1: parse time elapsed NAME VALUE ------------------------------------------------------ ---------- parse time elapsed 83 SQL> update emp set sal=sal+200; 15 rows updated. SQL> @mystat2 SQL> set echo off linesize 160 NAME V DIFF ------------------------------------------------------ ---------- ---------------- parse time elapsed 117 34

7、小结 a、关于SQLPlus执行脚本时的参数传递其实质是使用了替代变量&，可以参考:替代变量与SQL*Plus环境设置 b、new_value子句可以在脚本中传递变量，

通常的使用方法为:

column column_name new_value var_name

new_value是将所获得的列值赋予到变量名，然后该变量名可以参与后续处理

c、多个参数传递时，中间使用空格隔开

d、对于参数中本身含有空格的情形应使用引号将其括起来

【本文地址】

公司简介

联系我们